﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class PurchaseOrderDA : IPurchaseOrderDA
    {
        private string APPORTION_RULE = "@APPORTION_RULE";
        private string CARRIER = "@CARRIER";
        private string CLOSED_DATE = "@CLOSED_DATE";
        private string CREATED_BY = "@CREATED_BY";
        private string DEST_COUNTRY = "@DEST_COUNTRY";
        private string EXTERNAL_PO_ID = "@EXTERNAL_PO_ID";
        private string EXTERNAL_PO_ID2 = "@EXTERNAL_PO_ID2";
        private string INCO_TERMS = "@INCO_TERMS";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string OPEN_QTY = "@OPEN_QTY";
        private string OTHER_REF = "@OTHER_REF";
        private string OWNER_ADDRESS = "@OWNER_ADDRESS";
        private string OWNER_CITY = "@OWNER_CITY";
        private string OWNER_CONTACT = "@OWNER_CONTACT";
        private string OWNER_COUNTRY = "@OWNER_COUNTRY";
        private string OWNER_EMAIL = "@OWNER_EMAIL";
        private string OWNER_FAX = "@OWNER_FAX";
        private string OWNER_ID = "@OWNER_ID";
        private string OWNER_NAME = "@OWNER_NAME";
        private string OWNER_PHONE = "@OWNER_PHONE";
        private string OWNER_PROV = "@OWNER_PROV";
        private string OWNER_REF = "@OWNER_REF";
        private string OWNER_VAT = "@OWNER_VAT";
        private string OWNER_ZIP = "@OWNER_ZIP";
        private string PAYMENT_TERM = "@PAYMENT_TERM";
        private string PLACE_OF_DELIVERY = "@PLACE_OF_DELIVERY";
        private string PLACE_OF_DISCHARGE = "@PLACE_OF_DISCHARGE";
        private string PLACE_OF_LOADING = "@PLACE_OF_LOADING";
        private string PO_DATE = "@PO_DATE";
        private string PO_GROUP = "@PO_GROUP";
        private string PO_ID = "@PO_ID";
        private string PO_TYPE = "@PO_TYPE";
        private string REMARK = "@REMARK";
        private string REQUEST_DELIVERY_DATE = "@REQUEST_DELIVERY_DATE";
        private string SEAL_NO = "@SEAL_NO";
        private string SQL_CHECK_PURCHASE_ORDER_ID_UNIQUE = " SELECT COUNT(1) FROM @_@PURCHASE_ORDER WHERE PO_ID = @PO_ID ";
        private string SQL_DELETE_PURCHASE_ORDER = " DELETE FROM @_@PURCHASE_ORDER WHERE PO_ID = @PO_ID ";
        private string SQL_INSERT_PURCHASE_ORDER = " INSERT INTO @_@PURCHASE_ORDER ( WH_ID, PO_ID, EXTERNAL_PO_ID, PO_GROUP, PO_TYPE, PO_DATE, REQUEST_DELIVERY_DATE, CLOSED_DATE, STATUS, OTHER_REF, OWNER_ID, OWNER_NAME, OWNER_ADDRESS, OWNER_CITY, OWNER_PROV, OWNER_ZIP, OWNER_COUNTRY, OWNER_CONTACT, OWNER_FAX, OWNER_PHONE, OWNER_EMAIL, OWNER_VAT, OWNER_REF, VENDOR_ID, VENDOR_NAME, VENDOR_ADDRESS, VENDOR_CITY, VENDOR_PROV, VENDOR_ZIP, VENDOR_COUNTRY, VENDOR_CONTACT, VENDOR_FAX, VENDOR_PHONE, VENDOR_EMAIL, VENDOR_VAT, VENDOR_REF, SRC_COUNTRY, DEST_COUNTRY, PLACE_OF_LOADING, PLACE_OF_DISCHARGE, PLACE_OF_DELIVERY, INCO_TERMS, PAYMENT_TERM, TRANS_MODE, OPEN_QTY, EXTERNAL_PO_ID2, APPORTION_RULE, VESSEL, VOYAGE, SEAL_NO, VESSEL_DATE, CARRIER, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, IS_ACTIVE, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @PO_ID, @EXTERNAL_PO_ID, @PO_GROUP, @PO_TYPE, @PO_DATE, @REQUEST_DELIVERY_DATE, @CLOSED_DATE, @STATUS, @OTHER_REF, @OWNER_ID, @OWNER_NAME, @OWNER_ADDRESS, @OWNER_CITY, @OWNER_PROV, @OWNER_ZIP, @OWNER_COUNTRY, @OWNER_CONTACT, @OWNER_FAX, @OWNER_PHONE, @OWNER_EMAIL, @OWNER_VAT, @OWNER_REF, @VENDOR_ID, @VENDOR_NAME, @VENDOR_ADDRESS, @VENDOR_CITY, @VENDOR_PROV, @VENDOR_ZIP, @VENDOR_COUNTRY, @VENDOR_CONTACT, @VENDOR_FAX, @VENDOR_PHONE, @VENDOR_EMAIL, @VENDOR_VAT, @VENDOR_REF, @SRC_COUNTRY, @DEST_COUNTRY, @PLACE_OF_LOADING, @PLACE_OF_DISCHARGE, @PLACE_OF_DELIVERY, @INCO_TERMS, @PAYMENT_TERM, @TRANS_MODE, @OPEN_QTY, @EXTERNAL_PO_ID2, @APPORTION_RULE, @VESSEL, @VOYAGE, @SEAL_NO, @VESSEL_DATE, @CARRIER, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @IS_ACTIVE, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_PURCHASE_ORDER = " SELECT  PO.ROWID, PO.WH_ID, PO.PO_ID, PO.EXTERNAL_PO_ID, PO.PO_GROUP, PO.PO_TYPE,T2.[TEXT] AS PO_TYPE_D, PO.PO_DATE,PO.REQUEST_DELIVERY_DATE, PO.CLOSED_DATE, PO.STATUS,T.[TEXT] AS STATUS_D, PO.OTHER_REF, PO.OWNER_ID, PO.OWNER_NAME, PO.OWNER_ADDRESS, PO.OWNER_CITY, PO.OWNER_PROV, PO.OWNER_ZIP, PO.OWNER_COUNTRY, PO.OWNER_CONTACT, PO.OWNER_FAX, PO.OWNER_PHONE, PO.OWNER_EMAIL, PO.OWNER_VAT, PO.OWNER_REF, PO.VENDOR_ID, PO.VENDOR_NAME, PO.VENDOR_ADDRESS, PO.VENDOR_CITY, PO.VENDOR_PROV, PO.VENDOR_ZIP, PO.VENDOR_COUNTRY, PO.VENDOR_CONTACT, PO.VENDOR_FAX, PO.VENDOR_PHONE, PO.VENDOR_EMAIL, PO.VENDOR_VAT, PO.VENDOR_REF, PO.SRC_COUNTRY, PO.DEST_COUNTRY, PO.PLACE_OF_LOADING, PO.PLACE_OF_DISCHARGE, PO.PLACE_OF_DELIVERY, PO.INCO_TERMS, PO.PAYMENT_TERM, PO.TRANS_MODE, PO.OPEN_QTY, PO.EXTERNAL_PO_ID2, PO.APPORTION_RULE, PO.VESSEL, PO.VOYAGE, PO.SEAL_NO, PO.VESSEL_DATE, PO.CARRIER, PO.UDF1, PO.UDF2, PO.UDF3, PO.UDF4, PO.UDF5, PO.UDF6, PO.UDF7, PO.UDF8, PO.UDF9, PO.UDF10, PO.IS_ACTIVE, PO.TS, PO.REMARK, PO.CREATED_BY, PO.CREATED_DATE, PO.UPDATED_BY, PO.UPDATED_DATE   FROM  @_@PURCHASE_ORDER PO LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = PO.STATUS AND SCD.SYS_CODE_TYPE = 'PoStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = PO.PO_TYPE AND SCD2.SYS_CODE_TYPE = 'PoType' LEFT JOIN sysadm.TRANSLATION T ON 'PoStatus.'+PO.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'PoType.'+PO.PO_TYPE = T2.ITEM_ID   \t\t\tAND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON PO.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID ";
        private string SQL_UPDATE_PURCHASE_ORDER = " UPDATE @_@PURCHASE_ORDER SET WH_ID = @WH_ID, EXTERNAL_PO_ID = @EXTERNAL_PO_ID, PO_GROUP = @PO_GROUP, PO_TYPE = @PO_TYPE, PO_DATE = @PO_DATE, REQUEST_DELIVERY_DATE = @REQUEST_DELIVERY_DATE, CLOSED_DATE = @CLOSED_DATE, STATUS = @STATUS, OTHER_REF = @OTHER_REF, OWNER_ID = @OWNER_ID, OWNER_NAME = @OWNER_NAME, OWNER_ADDRESS = @OWNER_ADDRESS, OWNER_CITY = @OWNER_CITY, OWNER_PROV = @OWNER_PROV, OWNER_ZIP = @OWNER_ZIP, OWNER_COUNTRY = @OWNER_COUNTRY, OWNER_CONTACT = @OWNER_CONTACT, OWNER_FAX = @OWNER_FAX, OWNER_PHONE = @OWNER_PHONE, OWNER_EMAIL = @OWNER_EMAIL, OWNER_VAT = @OWNER_VAT, OWNER_REF = @OWNER_REF, VENDOR_ID = @VENDOR_ID, VENDOR_NAME = @VENDOR_NAME, VENDOR_ADDRESS = @VENDOR_ADDRESS, VENDOR_CITY = @VENDOR_CITY, VENDOR_PROV = @VENDOR_PROV, VENDOR_ZIP = @VENDOR_ZIP, VENDOR_COUNTRY = @VENDOR_COUNTRY, VENDOR_CONTACT = @VENDOR_CONTACT, VENDOR_FAX = @VENDOR_FAX, VENDOR_PHONE = @VENDOR_PHONE, VENDOR_EMAIL = @VENDOR_EMAIL, VENDOR_VAT = @VENDOR_VAT, VENDOR_REF = @VENDOR_REF, SRC_COUNTRY = @SRC_COUNTRY, DEST_COUNTRY = @DEST_COUNTRY, PLACE_OF_LOADING = @PLACE_OF_LOADING, PLACE_OF_DISCHARGE = @PLACE_OF_DISCHARGE, PLACE_OF_DELIVERY = @PLACE_OF_DELIVERY, INCO_TERMS = @INCO_TERMS, PAYMENT_TERM = @PAYMENT_TERM, TRANS_MODE = @TRANS_MODE, OPEN_QTY = @OPEN_QTY, EXTERNAL_PO_ID2 = @EXTERNAL_PO_ID2, APPORTION_RULE = @APPORTION_RULE, VESSEL = @VESSEL, VOYAGE = @VOYAGE, SEAL_NO = @SEAL_NO, VESSEL_DATE = @VESSEL_DATE, CARRIER = @CARRIER, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, IS_ACTIVE = @IS_ACTIVE, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE PO_ID = @PO_ID  ";
        private string SQL_UPDATE_PURCHASE_ORDER_STATUS = "UPDATE @_@PURCHASE_ORDER SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE PO_ID = @PO_ID";
        private string SQL_UPDATE_PURCHASE_ORDER_STATUS_CLOSE = "UPDATE @_@PURCHASE_ORDER SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),CLOSED_DATE=@CLOSED_DATE WHERE PO_ID = @PO_ID";
        private string SRC_COUNTRY = "@SRC_COUNTRY";
        private string STATUS = "@STATUS";
        private string TRANS_MODE = "@TRANS_MODE";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VENDOR_ADDRESS = "@VENDOR_ADDRESS";
        private string VENDOR_CITY = "@VENDOR_CITY";
        private string VENDOR_CONTACT = "@VENDOR_CONTACT";
        private string VENDOR_COUNTRY = "@VENDOR_COUNTRY";
        private string VENDOR_EMAIL = "@VENDOR_EMAIL";
        private string VENDOR_FAX = "@VENDOR_FAX";
        private string VENDOR_ID = "@VENDOR_ID";
        private string VENDOR_NAME = "@VENDOR_NAME";
        private string VENDOR_PHONE = "@VENDOR_PHONE";
        private string VENDOR_PROV = "@VENDOR_PROV";
        private string VENDOR_REF = "@VENDOR_REF";
        private string VENDOR_VAT = "@VENDOR_VAT";
        private string VENDOR_ZIP = "@VENDOR_ZIP";
        private string VESSEL = "@VESSEL";
        private string VESSEL_DATE = "@VESSEL_DATE";
        private string VOYAGE = "@VOYAGE";
        private string WH_ID = "@WH_ID";

        public bool CheckPurchaseOrderIDUnique(string purchaseOrderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", purchaseOrderID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_PURCHASE_ORDER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckPurchaseOrderIDUnique(DataBase dataBase, DbTransaction tran, string purchaseOrderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", purchaseOrderID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_PURCHASE_ORDER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeletePurchaseOrder(List<string> purchaseOrderID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < purchaseOrderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", purchaseOrderID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_PURCHASE_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeletePurchaseOrder(DataBase dataBase, DbTransaction tran, List<string> purchaseOrderID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < purchaseOrderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", purchaseOrderID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_PURCHASE_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllPurchaseOrder(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PURCHASE_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllPurchaseOrder(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PURCHASE_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public PurchaseOrderInfo GetPurchaseOrderByID(string purchaseorderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PURCHASE_ORDER.Replace("@_@", whLoginID) + " WHERE PO.PO_ID = @PO_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", purchaseorderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            PurchaseOrderInfo purchaseOrderInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    purchaseOrderInfo = new PurchaseOrderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PO_ID"].ToString(), reader["EXTERNAL_PO_ID"].ToString(), reader["PO_GROUP"].ToString(), reader["PO_TYPE"].ToString(), (reader["PO_DATE"].ToString() != "") ? DateTime.Parse(reader["PO_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["REQUEST_DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUEST_DELIVERY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["CLOSED_DATE"].ToString() != "") ? DateTime.Parse(reader["CLOSED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["OTHER_REF"].ToString(), reader["OWNER_ID"].ToString(), reader["OWNER_NAME"].ToString(), reader["OWNER_ADDRESS"].ToString(), reader["OWNER_CITY"].ToString(), reader["OWNER_PROV"].ToString(), reader["OWNER_ZIP"].ToString(), reader["OWNER_COUNTRY"].ToString(), reader["OWNER_CONTACT"].ToString(), reader["OWNER_FAX"].ToString(), reader["OWNER_PHONE"].ToString(), reader["OWNER_EMAIL"].ToString(), reader["OWNER_VAT"].ToString(), reader["OWNER_REF"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["VENDOR_ADDRESS"].ToString(), reader["VENDOR_CITY"].ToString(), reader["VENDOR_PROV"].ToString(), reader["VENDOR_ZIP"].ToString(), reader["VENDOR_COUNTRY"].ToString(), reader["VENDOR_CONTACT"].ToString(), reader["VENDOR_FAX"].ToString(), reader["VENDOR_PHONE"].ToString(), reader["VENDOR_EMAIL"].ToString(), reader["VENDOR_VAT"].ToString(), reader["VENDOR_REF"].ToString(), reader["SRC_COUNTRY"].ToString(), reader["DEST_COUNTRY"].ToString(), reader["PLACE_OF_LOADING"].ToString(), reader["PLACE_OF_DISCHARGE"].ToString(), reader["PLACE_OF_DELIVERY"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["TRANS_MODE"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["EXTERNAL_PO_ID2"].ToString(), reader["APPORTION_RULE"].ToString(), reader["VESSEL"].ToString(), reader["VOYAGE"].ToString(), reader["SEAL_NO"].ToString(), (reader["VESSEL_DATE"].ToString() != "") ? DateTime.Parse(reader["VESSEL_DATE"].ToString()) : (CS00003 = new DateTime()), reader["CARRIER"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["REMARK"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return purchaseOrderInfo;
        }

        public PurchaseOrderInfo GetPurchaseOrderByID(DataBase dataBase, DbTransaction tran, string purchaseorderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PURCHASE_ORDER.Replace("@_@", whLoginID) + " WHERE PO.PO_ID = @PO_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", purchaseorderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            PurchaseOrderInfo purchaseOrderInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                purchaseOrderInfo = new PurchaseOrderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PO_ID"].ToString(), reader["EXTERNAL_PO_ID"].ToString(), reader["PO_GROUP"].ToString(), reader["PO_TYPE"].ToString(), (reader["PO_DATE"].ToString() != "") ? DateTime.Parse(reader["PO_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["REQUEST_DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUEST_DELIVERY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["CLOSED_DATE"].ToString() != "") ? DateTime.Parse(reader["CLOSED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["OTHER_REF"].ToString(), reader["OWNER_ID"].ToString(), reader["OWNER_NAME"].ToString(), reader["OWNER_ADDRESS"].ToString(), reader["OWNER_CITY"].ToString(), reader["OWNER_PROV"].ToString(), reader["OWNER_ZIP"].ToString(), reader["OWNER_COUNTRY"].ToString(), reader["OWNER_CONTACT"].ToString(), reader["OWNER_FAX"].ToString(), reader["OWNER_PHONE"].ToString(), reader["OWNER_EMAIL"].ToString(), reader["OWNER_VAT"].ToString(), reader["OWNER_REF"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["VENDOR_ADDRESS"].ToString(), reader["VENDOR_CITY"].ToString(), reader["VENDOR_PROV"].ToString(), reader["VENDOR_ZIP"].ToString(), reader["VENDOR_COUNTRY"].ToString(), reader["VENDOR_CONTACT"].ToString(), reader["VENDOR_FAX"].ToString(), reader["VENDOR_PHONE"].ToString(), reader["VENDOR_EMAIL"].ToString(), reader["VENDOR_VAT"].ToString(), reader["VENDOR_REF"].ToString(), reader["SRC_COUNTRY"].ToString(), reader["DEST_COUNTRY"].ToString(), reader["PLACE_OF_LOADING"].ToString(), reader["PLACE_OF_DISCHARGE"].ToString(), reader["PLACE_OF_DELIVERY"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["TRANS_MODE"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["EXTERNAL_PO_ID2"].ToString(), reader["APPORTION_RULE"].ToString(), reader["VESSEL"].ToString(), reader["VOYAGE"].ToString(), reader["SEAL_NO"].ToString(), (reader["VESSEL_DATE"].ToString() != "") ? DateTime.Parse(reader["VESSEL_DATE"].ToString()) : (CS00003 = new DateTime()), reader["CARRIER"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["REMARK"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return purchaseOrderInfo;
        }

        public DataSet GetPurchaseOrderByQueryList(List<string> sqlWhere, PurchaseOrderQueryEntity purchaseorderQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_PURCHASE_ORDER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!purchaseorderQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, purchaseorderQuery.CurrentPage, purchaseorderQuery.PageSize, purchaseorderQuery.SortField, purchaseorderQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetPurchaseOrderByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, PurchaseOrderQueryEntity purchaseorderQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_PURCHASE_ORDER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!purchaseorderQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, purchaseorderQuery.CurrentPage, purchaseorderQuery.PageSize, purchaseorderQuery.SortField, purchaseorderQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertPurchaseOrder(PurchaseOrderInfo purchaseOrderInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    purchaseOrderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_PurchaseOrder_Parameters(purchaseOrderInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_PURCHASE_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertPurchaseOrder(DataBase dataBase, DbTransaction tran, PurchaseOrderInfo purchaseOrderInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    purchaseOrderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_PurchaseOrder_Parameters(purchaseOrderInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_PURCHASE_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_PurchaseOrder_Parameters(PurchaseOrderInfo purchaseOrderInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.PO_ID, SqlDbType.VarChar, 20), new SqlParameter(this.EXTERNAL_PO_ID, SqlDbType.VarChar, 50), new SqlParameter(this.PO_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.PO_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.PO_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.REQUEST_DELIVERY_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.CLOSED_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.OTHER_REF, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_NAME, SqlDbType.VarChar, 50), new SqlParameter(this.OWNER_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.OWNER_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.OWNER_PROV, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ZIP, SqlDbType.VarChar, 20), 
                new SqlParameter(this.OWNER_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.OWNER_CONTACT, SqlDbType.VarChar, 30), new SqlParameter(this.OWNER_FAX, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_PHONE, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_EMAIL, SqlDbType.VarChar, 60), new SqlParameter(this.OWNER_VAT, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_REF, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_NAME, SqlDbType.VarChar, 50), new SqlParameter(this.VENDOR_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.VENDOR_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.VENDOR_PROV, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.VENDOR_CONTACT, SqlDbType.VarChar, 30), new SqlParameter(this.VENDOR_FAX, SqlDbType.VarChar, 20), 
                new SqlParameter(this.VENDOR_PHONE, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_EMAIL, SqlDbType.VarChar, 60), new SqlParameter(this.VENDOR_VAT, SqlDbType.VarChar, 0x12), new SqlParameter(this.VENDOR_REF, SqlDbType.VarChar, 20), new SqlParameter(this.SRC_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.DEST_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.PLACE_OF_LOADING, SqlDbType.VarChar, 500), new SqlParameter(this.PLACE_OF_DISCHARGE, SqlDbType.VarChar, 500), new SqlParameter(this.PLACE_OF_DELIVERY, SqlDbType.VarChar, 500), new SqlParameter(this.INCO_TERMS, SqlDbType.VarChar, 10), new SqlParameter(this.PAYMENT_TERM, SqlDbType.VarChar, 10), new SqlParameter(this.TRANS_MODE, SqlDbType.VarChar, 10), new SqlParameter(this.OPEN_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.EXTERNAL_PO_ID2, SqlDbType.VarChar, 50), new SqlParameter(this.APPORTION_RULE, SqlDbType.VarChar, 10), new SqlParameter(this.VESSEL, SqlDbType.VarChar, 50), 
                new SqlParameter(this.VOYAGE, SqlDbType.VarChar, 20), new SqlParameter(this.SEAL_NO, SqlDbType.VarChar, 20), new SqlParameter(this.VESSEL_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.CARRIER, SqlDbType.VarChar, 20), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x3e8), 
                new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(purchaseOrderInfo.WhID))
            {
                paramArray[0].Value = purchaseOrderInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = purchaseOrderInfo.PoID;
            if (!string.IsNullOrEmpty(purchaseOrderInfo.ExternalPoID))
            {
                paramArray[2].Value = purchaseOrderInfo.ExternalPoID;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            paramArray[3].Value = purchaseOrderInfo.PoGroup;
            if (!string.IsNullOrEmpty(purchaseOrderInfo.PoType))
            {
                paramArray[4].Value = purchaseOrderInfo.PoType;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            DateTime CS00003 = new DateTime();
            if (purchaseOrderInfo.PoDate != CS00003)
            {
                paramArray[5].Value = purchaseOrderInfo.PoDate;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (purchaseOrderInfo.RequestDeliveryDate != CS00003)
            {
                paramArray[6].Value = purchaseOrderInfo.RequestDeliveryDate;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (purchaseOrderInfo.ClosedDate != CS00003)
            {
                paramArray[7].Value = purchaseOrderInfo.ClosedDate;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            paramArray[8].Value = purchaseOrderInfo.Status;
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OtherRef))
            {
                paramArray[9].Value = purchaseOrderInfo.OtherRef;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            paramArray[10].Value = purchaseOrderInfo.OwnerID;
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerName))
            {
                paramArray[11].Value = purchaseOrderInfo.OwnerName;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerAddress))
            {
                paramArray[12].Value = purchaseOrderInfo.OwnerAddress;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerCity))
            {
                paramArray[13].Value = purchaseOrderInfo.OwnerCity;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerProv))
            {
                paramArray[14].Value = purchaseOrderInfo.OwnerProv;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerZip))
            {
                paramArray[15].Value = purchaseOrderInfo.OwnerZip;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerCountry))
            {
                paramArray[0x10].Value = purchaseOrderInfo.OwnerCountry;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerContact))
            {
                paramArray[0x11].Value = purchaseOrderInfo.OwnerContact;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerFax))
            {
                paramArray[0x12].Value = purchaseOrderInfo.OwnerFax;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerPhone))
            {
                paramArray[0x13].Value = purchaseOrderInfo.OwnerPhone;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerEmail))
            {
                paramArray[20].Value = purchaseOrderInfo.OwnerEmail;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerVat))
            {
                paramArray[0x15].Value = purchaseOrderInfo.OwnerVat;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.OwnerRef))
            {
                paramArray[0x16].Value = purchaseOrderInfo.OwnerRef;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorID))
            {
                paramArray[0x17].Value = purchaseOrderInfo.VendorID;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorName))
            {
                paramArray[0x18].Value = purchaseOrderInfo.VendorName;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorAddress))
            {
                paramArray[0x19].Value = purchaseOrderInfo.VendorAddress;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorCity))
            {
                paramArray[0x1a].Value = purchaseOrderInfo.VendorCity;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorProv))
            {
                paramArray[0x1b].Value = purchaseOrderInfo.VendorProv;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorZip))
            {
                paramArray[0x1c].Value = purchaseOrderInfo.VendorZip;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorCountry))
            {
                paramArray[0x1d].Value = purchaseOrderInfo.VendorCountry;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorContact))
            {
                paramArray[30].Value = purchaseOrderInfo.VendorContact;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorFax))
            {
                paramArray[0x1f].Value = purchaseOrderInfo.VendorFax;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorPhone))
            {
                paramArray[0x20].Value = purchaseOrderInfo.VendorPhone;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorEmail))
            {
                paramArray[0x21].Value = purchaseOrderInfo.VendorEmail;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorVat))
            {
                paramArray[0x22].Value = purchaseOrderInfo.VendorVat;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.VendorRef))
            {
                paramArray[0x23].Value = purchaseOrderInfo.VendorRef;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.SrcCountry))
            {
                paramArray[0x24].Value = purchaseOrderInfo.SrcCountry;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.DestCountry))
            {
                paramArray[0x25].Value = purchaseOrderInfo.DestCountry;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.PlaceOfLoading))
            {
                paramArray[0x26].Value = purchaseOrderInfo.PlaceOfLoading;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.PlaceOfDischarge))
            {
                paramArray[0x27].Value = purchaseOrderInfo.PlaceOfDischarge;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.PlaceOfDelivery))
            {
                paramArray[40].Value = purchaseOrderInfo.PlaceOfDelivery;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.IncoTerms))
            {
                paramArray[0x29].Value = purchaseOrderInfo.IncoTerms;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.PaymentTerm))
            {
                paramArray[0x2a].Value = purchaseOrderInfo.PaymentTerm;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.TransMode))
            {
                paramArray[0x2b].Value = purchaseOrderInfo.TransMode;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            paramArray[0x2c].Value = purchaseOrderInfo.OpenQty;
            if (!string.IsNullOrEmpty(purchaseOrderInfo.ExternalPoId2))
            {
                paramArray[0x2d].Value = purchaseOrderInfo.ExternalPoId2;
            }
            else
            {
                paramArray[0x2d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.ApportionRule))
            {
                paramArray[0x2e].Value = purchaseOrderInfo.ApportionRule;
            }
            else
            {
                paramArray[0x2e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Vessel))
            {
                paramArray[0x2f].Value = purchaseOrderInfo.Vessel;
            }
            else
            {
                paramArray[0x2f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Voyage))
            {
                paramArray[0x30].Value = purchaseOrderInfo.Voyage;
            }
            else
            {
                paramArray[0x30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.SealNo))
            {
                paramArray[0x31].Value = purchaseOrderInfo.SealNo;
            }
            else
            {
                paramArray[0x31].Value = DBNull.Value;
            }
            if (purchaseOrderInfo.VesselDate != new DateTime())
            {
                paramArray[50].Value = purchaseOrderInfo.VesselDate;
            }
            else
            {
                paramArray[50].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Carrier))
            {
                paramArray[0x33].Value = purchaseOrderInfo.Carrier;
            }
            else
            {
                paramArray[0x33].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Udf1))
            {
                paramArray[0x34].Value = purchaseOrderInfo.Udf1;
            }
            else
            {
                paramArray[0x34].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Udf2))
            {
                paramArray[0x35].Value = purchaseOrderInfo.Udf2;
            }
            else
            {
                paramArray[0x35].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Udf3))
            {
                paramArray[0x36].Value = purchaseOrderInfo.Udf3;
            }
            else
            {
                paramArray[0x36].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Udf4))
            {
                paramArray[0x37].Value = purchaseOrderInfo.Udf4;
            }
            else
            {
                paramArray[0x37].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Udf5))
            {
                paramArray[0x38].Value = purchaseOrderInfo.Udf5;
            }
            else
            {
                paramArray[0x38].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Udf6))
            {
                paramArray[0x39].Value = purchaseOrderInfo.Udf6;
            }
            else
            {
                paramArray[0x39].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Udf7))
            {
                paramArray[0x3a].Value = purchaseOrderInfo.Udf7;
            }
            else
            {
                paramArray[0x3a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Udf8))
            {
                paramArray[0x3b].Value = purchaseOrderInfo.Udf8;
            }
            else
            {
                paramArray[0x3b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Udf9))
            {
                paramArray[60].Value = purchaseOrderInfo.Udf9;
            }
            else
            {
                paramArray[60].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Udf10))
            {
                paramArray[0x3d].Value = purchaseOrderInfo.Udf10;
            }
            else
            {
                paramArray[0x3d].Value = DBNull.Value;
            }
            paramArray[0x3e].Value = purchaseOrderInfo.IsActive;
            if (!string.IsNullOrEmpty(purchaseOrderInfo.Remark))
            {
                paramArray[0x3f].Value = purchaseOrderInfo.Remark;
            }
            else
            {
                paramArray[0x3f].Value = DBNull.Value;
            }
            paramArray[0x40].Value = purchaseOrderInfo.CreatedBy;
            paramArray[0x41].Value = purchaseOrderInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdatePurchaseOrder(PurchaseOrderInfo purchaseOrderInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    purchaseOrderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_PurchaseOrder_Parameters(purchaseOrderInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PURCHASE_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdatePurchaseOrder(DataBase dataBase, DbTransaction tran, PurchaseOrderInfo purchaseOrderInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    purchaseOrderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_PurchaseOrder_Parameters(purchaseOrderInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PURCHASE_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdatePurchaseOrderStatus(string poID, string status, string whLoginID, string updatedBy)
        {
            string sql = this.SQL_UPDATE_PURCHASE_ORDER_STATUS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@PO_ID", poID) };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
        }

        public int UpdatePurchaseOrderStatus(DataBase dataBase, DbTransaction tran, string poID, string status, string whLoginID, string updatedBy)
        {
            string sql = string.Empty;
            SqlParameter[] paras = null;
            if (status == PurchaseOrderStatus.Closed)
            {
                sql = this.SQL_UPDATE_PURCHASE_ORDER_STATUS_CLOSE.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@CLOSED_DATE", DateTime.Now), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@PO_ID", poID) };
            }
            else
            {
                sql = this.SQL_UPDATE_PURCHASE_ORDER_STATUS.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@PO_ID", poID) };
            }
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
        }
    }
}

